﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data.SqlClient;
using System.Globalization;
using System.Data;

namespace University.Workshop.DataAccess
{
    public static class UserWishListDataAccess
    {
        private const string ConnectionString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";

        public static UserWishList InsertWishList(UserWishList userWishList)
        {
            if (userWishList != null)
            {
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(ConnectionString);
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "INSERT INTO USERWISHLIST (UserId,ProductId,Status) VALUES (@UserId,@ProductId,@Status)";
                    command.Parameters.AddWithValue("UserId",userWishList.UserId);
                    command.Parameters.AddWithValue("ProductId",userWishList.ProductId);
                    command.Parameters.AddWithValue("Status",userWishList.Status);
                    SqlCommand getID = connection.CreateCommand();
                    getID.CommandText = "SELECT @@Identity";

                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error getting an User's WishList");
                    }
                    userWishList.WishListId = Convert.ToInt64(getID.ExecuteScalar(),CultureInfo.CurrentCulture);

                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error inserting an User's WishList", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return userWishList;
        }


        public static object ViewWishList(UserWishList userWL)
        {
            if (userWL != null)
            {
                SqlConnection connection = null;
                DataView dataView = null;
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = null;
                try
                {

                    ds.Locale = CultureInfo.CurrentCulture;
                    connection = new SqlConnection(ConnectionString);
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT ID,Name,Category,Price,Currency,PosDate,Status FROM PRODUCT WHERE ID IN (SELECT ProductId FROM USERWISHLIST WHERE UserId=@userId and ProductId=ID)";
                    command.Parameters.AddWithValue("UserId", userWL.UserId);
                    connection.Open();
                    adapter = new SqlDataAdapter(command);
                    adapter.Fill(ds, "PRODUCT");
                    if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                    dataView = ds.Tables["PRODUCT"].DefaultView;
                    return dataView;

                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error getting wishlist", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    if (ds != null)
                    {
                        ds.Dispose();
                    }
                    if (adapter != null)
                    {
                        adapter.Dispose();
                    }
                    SqlConnection.ClearAllPools();
                }
                
            }
            return null;
        }

        public static object GetProductBYUserId(long userId)
        {
            SqlConnection connection = null;
            DataView dataView = null;
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = null;
            try
            {

                ds.Locale = CultureInfo.CurrentCulture;
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT ProductId FROM USERWISHLIST WHERE UserId=@userId ";   
                command.Parameters.AddWithValue("UserId", userId);
                connection.Open();
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, "USERWISHLIST");
                if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                dataView = ds.Tables["USERWISHLIST"].DefaultView;
                return dataView;

            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting wishlist", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                if (ds != null)
                {
                    ds.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static object GetUnavailableProducts(long userId)
        {
            SqlConnection connection = null;
            DataView dataView = null;
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = null;
            try
            {

                ds.Locale = CultureInfo.CurrentCulture;
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT ProductId FROM USERWISHLIST WHERE UserId=@userId and Status='Unavailable' ";
                command.Parameters.AddWithValue("UserId", userId);
                connection.Open();
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, "USERWISHLIST");
                if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                dataView = ds.Tables["USERWISHLIST"].DefaultView;
                return dataView;

            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting unavailable products of wishlist", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                if (ds != null)
                {
                    ds.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static void UpdateProductStatus(long productId) //change status form available to unavailable
        {
            SqlConnection connection = null;
            connection = new SqlConnection(ConnectionString);
            try 
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "UPDATE USERWISHLIST SET Status='Unavailable' WHERE ProductId=@productId";
                command.Parameters.AddWithValue("ProductId",productId);
                connection.Open();
                int result = command.ExecuteNonQuery();
                if (result <= 0)
                {
                    throw new DataAccessException("Error updating product status of wishlist");
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error updating product status of wishlist", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }

        }

        public static object GetProductBYProductId(long productId)
        {
            SqlConnection connection = null;
            DataView dataView = null;
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = null;
            try
            {

                ds.Locale = CultureInfo.CurrentCulture;
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT ProductId FROM USERWISHLIST WHERE ProductId=@productId ";
                command.Parameters.AddWithValue("ProductId", productId);
                connection.Open();
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, "USERWISHLIST");
                if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                dataView = ds.Tables["USERWISHLIST"].DefaultView;
                return dataView;

            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting wishlist", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                if (ds != null)
                {
                    ds.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static void UpdateProductStatusAvailable(long productId) //change status form available to unavailable
        {
            SqlConnection connection = null;
            connection = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "UPDATE USERWISHLIST SET Status='Available' WHERE ProductId=@productId";
                command.Parameters.AddWithValue("ProductId", productId);
                connection.Open();
                int result = command.ExecuteNonQuery();
                if (result <= 0)
                {
                    throw new DataAccessException("Error updating product status of wishlist");
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error updating product status of wishlist", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }

        }

        public static void DeleteProductWishList(long productId)
        {

            SqlConnection connection = null;
            connection = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "DELETE FROM USERWISHLIST WHERE ProductId=@productId";
                command.Parameters.AddWithValue("ProductId", productId);
                connection.Open();
                int result = command.ExecuteNonQuery();
                if (result <= 0)
                {
                    throw new DataAccessException("Error deleting product in wishlist");
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error deleting product in wishlist", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static object UserEmail(long productId) // if a product in wishlist is available again get the emails to send a notification
        {
            SqlConnection connection = null;
            DataView dataView = null;
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = null;
            try
            {

                ds.Locale = CultureInfo.CurrentCulture;
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT Email FROM [USER] WHERE Id in(SELECT UserId FROM USERWISHLIST WHERE ProductId=@productId) ";
                command.Parameters.AddWithValue("ProductId", productId);
                connection.Open();
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, "[USER]");
                if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                dataView = ds.Tables["[USER]"].DefaultView;
                return dataView;

            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting user email", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                if (ds != null)
                {
                    ds.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static object WishListProducts()
        {
            
                SqlConnection connection = null;
                DataView dataView = null;
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = null;
                try
                {

                    ds.Locale = CultureInfo.CurrentCulture;
                    connection = new SqlConnection(ConnectionString);
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT ProductId FROM USERWISHLIST ";
                    connection.Open();
                    adapter = new SqlDataAdapter(command);
                    adapter.Fill(ds, "USERWISHLIST");
                    if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                    dataView = ds.Tables["USERWISHLIST"].DefaultView;
                    return dataView;

                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error getting wishlist", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    if (ds != null)
                    {
                        ds.Dispose();
                    }
                    if (adapter != null)
                    {
                        adapter.Dispose();
                    }
                    SqlConnection.ClearAllPools();
                }

        }

    }
}
